﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PhysicalDataProvider
{
    public class SqlGenerator
    {
        public virtual string InsertSql
             (string[] cols, string table)
        {
            StringBuilder sql
                = new StringBuilder("INSERT INTO ");
            sql.Append(GetTableName(table) + " (");
            sql.Append(GetColums(cols) + ") VALUES (");
            sql.Append(GetParams(cols) + ")");

            return sql.ToString();
        }

        public string InsertSql
            (string col, string table)
        {
            return this.InsertSql(new string[] { col }, table);
        }

        // access
        public virtual string GetTableName(string table)
        {
            return "[" + table + "]";
        }

        public virtual string GetParams(string[] cols)
        {
            string[] strs = new string[cols.Length];

            for (int i = 0; i < cols.Length; i++)
            {
                strs[i] = GetParameterName(cols[i]);
            }

            return string.Join(",", strs);
        }

        public virtual string GetColums(string[] cols)
        {
            string[] strs = new string[cols.Length];

            for (int i = 0; i < cols.Length; i++)
            {
                strs[i] = GetColumnName(cols[i]);
            }

            return string.Join(",", strs);
        }

        public virtual string GetColumnName(string col)
        {
            return "[" + col + "]";
        }

        public virtual string GetParameterName(string col)
        {
            return "?";
        }

        public virtual string DeleteSql(string idCol, string table)
        {
            StringBuilder sql
                = new StringBuilder("DELETE FROM ");
            sql.Append(GetTableName(table) + " WHERE " + GetColumnName(idCol) + "=");
            sql.Append(GetParameterName(idCol));

            return sql.ToString();
        }

        public virtual string UpdateSql
            (string idCol, string[] cols, string table)
        {
            StringBuilder sql
                = new StringBuilder("UPDATE " + GetTableName(table));
            sql.Append(" SET ");

            foreach (string col in cols)
            {
                sql.AppendFormat("{0}={1},", col, GetParameterName(col));
            }
            sql.Remove(sql.Length - 1, 1);
            sql.AppendFormat(" WHERE {0}={1}", GetColumnName(idCol), GetParameterName(idCol));
            return sql.ToString();
        }

        public virtual string SelectSql
            (string[] cols, string table, string where)
        {
            StringBuilder sb = new StringBuilder("SELECT ");
            sb.Append(GetColums(cols) + " FROM " + GetTableName(table)
                );
            if (!String.IsNullOrEmpty(where))
            {
                sb.Append(" WHERE " + where);
            }

            return sb.ToString();
        }

        public virtual string GetScalarSql
            (string[] cols, string table, string idCol)
        {
            StringBuilder sb = new StringBuilder("SELECT ");
            sb.Append(this.GetColums(cols) + " FROM " + GetTableName(table)
                );
            if (!String.IsNullOrEmpty(idCol))
            {
                sb.Append(" WHERE " + GetColumnName(idCol) + "=" + GetParameterName(idCol));
            }

            return sb.ToString();
        }

    }
}
